using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using DTO;
using System.Data;
using System.Collections;

namespace DAOLayer
{
    public class MuonSachDao
    {
        public void ThemMuonSach(MuonSachDto ms)
        {
            OleDbConnection cn = Database.ConnectionData();
            string str = "Insert into Muon (Isbn,MaCuonSach,MaDocGia,NgayMuon,NgayHetHan) values(?,?,?,?,?)";
            OleDbCommand cmd = new OleDbCommand(str, cn);
            cmd.Parameters.Add("@Isbn", OleDbType.Integer);
            cmd.Parameters["@Isbn"].Value = ms.Isbn;

            cmd.Parameters.Add("@MaCuonSach", OleDbType.WChar);
            cmd.Parameters["@MaCuonSach"].Value = ms.MaCuonSach;

            cmd.Parameters.Add("@MaDocGia", OleDbType.WChar);
            cmd.Parameters["@MaDocGia"].Value = ms.MaDocGia;

            cmd.Parameters.Add("@NgayMuon", OleDbType.Date);
            cmd.Parameters["@NgayMuon"].Value = ms.NgayMuon;

            cmd.Parameters.Add("@NgayHetHan", OleDbType.Date);
            cmd.Parameters["@NgayHetHan"].Value = ms.NgayHetHan;


            cmd.ExecuteNonQuery();
            str = "Select @@IDENTITY";
            cmd = new OleDbCommand(str, cn);
            ms.Isbn = (int)cmd.ExecuteScalar();
            cn.Close();
        }


        public void XoaMuonSach(string macs,string madg)
        {
            OleDbConnection cn = Database.ConnectionData();
            string strSQL;
            strSQL = "Delete From Muon Where MaCuonSach = ? and MaDocGia = ? ";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            //cmd.Parameters.Add("@Isbn", OleDbType.Integer);
            //cmd.Parameters["@Isbn"].Value = isbn;

            cmd.Parameters.Add("@MaCuonSach", OleDbType.WChar);
            cmd.Parameters["@MaCuonSach"].Value = macs;

            cmd.Parameters.Add("@MaDocGia", OleDbType.WChar);
            cmd.Parameters["@MaDocGia"].Value = madg;

            cmd.ExecuteNonQuery();
            cn.Close();
        }

        public void CapNhatBangMuonSach(DataTable dt)
        {
            OleDbConnection cn;
            cn = Database.ConnectionData();
            string strSQL;
            strSQL = "Select * From Muon";
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
            da.Update(dt);
            cn.Close();
        }



        public DataTable LayBangMuonSach()
        {
            DataTable dt = new DataTable();
            OleDbConnection cn = Database.ConnectionData();
            string strSQL = "Select * From Muon";
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(strSQL, cn);
            da.Fill(dt);
            cn.Close();
            return dt;
        }

        public ArrayList LayDanhSachMuonSach()
        {
            ArrayList ds = new ArrayList();
            OleDbConnection cn = Database.ConnectionData();
            string strSQL;
            strSQL = "Select * From Muon";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            OleDbDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                MuonSachDto mDto = new MuonSachDto();
                mDto.Isbn = (int)dr["Isbn"];
                mDto.MaCuonSach = (string)dr["MaCuonSach"];
                mDto.MaDocGia = (string)dr["MaDocGia"];
                mDto.NgayMuon = (DateTime)dr["NgayMuon"];
                mDto.NgayHetHan = (DateTime)dr["NgayHetHan"];
                ds.Add(mDto);
            }

            dr.Close();
            cn.Close();
            return ds;
        }

    }
}
